﻿using System.Data.SQLite;
using System.IO;
using System.Collections.Generic;

public class WordService
{
    private string _dbPath = "WordsDatabase.db"; // 数据库文件名
    private string _connectionString; // 数据库连接字符串

    public WordService()
    {
        _connectionString = $"Data Source={_dbPath};Version=3;";
        InitializeDatabase(); // 自动初始化数据库和表
    }

    // 初始化数据库（如果不存在则创建）
    private void InitializeDatabase()
    {
        if (!File.Exists(_dbPath))
        {
            SQLiteConnection.CreateFile(_dbPath);
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                CreateTable(conn); // 创建表
                InsertTestData(conn); // 插入测试数据
            }
        }
    }

    // 创建表
    private void CreateTable(SQLiteConnection conn)
    {
        string sql = @"
            CREATE TABLE Words (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                English TEXT NOT NULL,
                Chinese TEXT NOT NULL
            );";
        ExecuteNonQuery(sql, conn);
    }

    // 插入测试数据
    private void InsertTestData(SQLiteConnection conn)
    {
        string sql = @"
            INSERT INTO Words (English, Chinese) VALUES
            ('apple', '苹果'),
            ('computer', '电脑'),
            ('hello', '你好'),
            ('book', '书'),
            ('water', '水');";
        ExecuteNonQuery(sql, conn);
    }

    // 执行非查询 SQL 命令
    private void ExecuteNonQuery(string sql, SQLiteConnection conn)
    {
        using (var cmd = new SQLiteCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }

    // 获取所有单词
    public List<(string English, string Chinese)> GetAllWords()
    {
        var words = new List<(string, string)>();
        using (var conn = new SQLiteConnection(_connectionString))
        {
            conn.Open();
            string sql = "SELECT English, Chinese FROM Words";
            using (var cmd = new SQLiteCommand(sql, conn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        words.Add((
                            reader["English"].ToString(),
                            reader["Chinese"].ToString()
                        ));
                    }
                }
            }
        }
        return words;
    }
}